#!/usr/bin/python3
# -*- coding:utf-8 -*-
"""
@File: DBCompare.py
@Author: lijk34925 
@Date: 2024/9/14 16:02
@desc: 请补充本模块功能说明
"""
import json
import time

from BladeFerm.Lib.Common import logger

from BladeFerm.ApiModule.httpRequest import HttpRequest
from BladeFerm.utils.excel_operate import ExcelOfPandas


class DBCompare:
    def __init__(self, kwargs):
        self.params = kwargs
        self.__init_params()
        self.HTTP = HttpRequest()
        self.EOP = ExcelOfPandas()
        self.res_filepath = ""

    def __init_params(self):
        self.config = {
            'url': 'https://blade.hundsun.com/openapi/datacompare/addTableStructureCompare.json',
            'api_token': 'uRlCPWWoqW4j5wshw2xPlq1V-yAegY4vVNTLbgl9Nih5NefRsTPsYfyaQ7bCQQzhffcGf2siMtV17idl-pUPNg',
            'schemas': ['HSRAW', 'HSPBS', 'HSRCCE', 'HSWFL', 'HSFUND',
                        'HSAMLBD', 'HSAMLPS', 'HSAMLRA', 'HSAMLRC', 'HSAMLWLCI', 'HSAMLLS',
                        'HSAMLWL', 'HSAMLWLCP', 'HSAMLWLCRS'],

            'Mysql': {'db_type': '0001', 'db_conn': 'jdbc:mysql://10.20.46.217:33061/sys?user=root&password=fxq5@123'},
            'TdsqlForMysql': {'db_type': '0006',
                              'db_conn': 'jdbc:mysql://10.20.249.156:15001/sys?user=tdsqlpcloud&password=admin@111'},

            'Oracle': {'db_type': '0002',
                       'db_conn': 'jdbc:oracle:thin:@10.20.20.182:1521/hsaml?user=system&password=system'},
            'OraclePerfMa': {'db_type': '0002',
                       'db_conn': 'jdbc:oracle:thin:@10.20.181.115:1521/orcl?user=hsfund&password=hsfund'},
            'OceanBaseForOracle': {'db_type': '0011',
                                   'db_conn': 'jdbc:oceanbase://10.20.194.171:2883?user=sys@oboracle#obdemo&password=aaAA11__'},
            'DaMeng': {'db_type': '0003', 'db_conn': 'jdbc:dm://10.20.194.172:5236?user=SYSDBA&password=DMdb_2023'},

            'PG': {
                'db_type': '0004',
                'db_conn': 'jdbc:postgresql://10.20.37.112:5432/hsrcm?user=hsfund&password=hsfund'
            }
        }
        if self.params:
            self.config.update(self.params)
        self.title = ["序号", "场景名称", "源库", "目标库", "用户", "任务ID", "比对报告URL", "源库URL", "目标库URL"]
        self.report = list()
        self.report.append(self.title)

    def compare_table_struct(self, source='Oracle', target='OceanBaseForOracle', schemas: list=None):
        """
        表结构信息比对并生成报告。对比详情 detail_info 配置说明如下：
            table子项包含：基础(basic)、字段类型(field,)、主键(primaryKey)、外键(foreignKey)、索引(index)、触发器(trigger)、默认值(defaultValue)
            view子项包含：基础(basic)
            function子项包含：基础(basic)
        :param source:
        :param target:
        :param schemas:
        :return:
        """
        root_path = self.config.get('path', "../static/DBDiffResult/")
        self.res_filepath = f"{root_path}数据库表结构比对结果_{source}_To_{target}_{time.time()}.xlsx"
        body_data = {
            "api_token": self.config.get('api_token'),
            "account": "lijk34925",
            "src_db_type": self.config.get(source).get('db_type'),
            "src_db_conn": self.config.get(source).get('db_conn'),
            "src_db_inst": "HSFUND",
            "tar_db_type": self.config.get(target).get('db_type'),
            "tar_db_conn": self.config.get(target).get('db_conn'),
            "tar_db_inst": "HSFUND",
            "detail_info": {
                "table": ["basic", "field", "primaryKey", "foreignKey", "index"],
                "view": ["basic"],
                "function": ["basic"],
            },
            "table_name_mappings": [
                {"src_table_name": "fnd_client_offline", "tar_table_name": "fnd_client_offline"}
            ]
        }
        headers = {"Content-Type": "application/json;charset=UTF-8"}
        index = 1
        schemas = self.config.get('schemas')
        for schema in schemas:
            body_data.update({"src_db_inst": schema, "tar_db_inst": schema})
            response = self.HTTP.doPost(self.config.get('url'), data=json.dumps(body_data), headers=headers)
            logger.info(response)
            if response.status_code == 200:
                res_data = response.json()
                logger.info(f"res_data: {res_data}")
                result_row = [index, "表结构比对", source, target, schema, res_data.get('data').get('task_id'),
                              res_data.get('data').get('url'), body_data.get("src_db_conn"), body_data.get("tar_db_conn")]
            else:
                logger.warning(response.json())
                result_row = [index, "表结构比对", source, target, schema, "", "", body_data.get("src_db_inst"),
                              body_data.get("tar_db_inst")]
            self.report.append(result_row)
            index += 1
        logger.info(f"比对结果完成")

    def __write_excel_report(self):
        df_data = self.EOP.data_to_DF(self.report)
        self.EOP.write_excel(self.res_filepath, df_data, sheet="比对结果", index=False)
        logger.info(f"比对结果已保存至: {self.res_filepath}")

    def compare_table_data(self, schemas: list):
        pass

    def act_compare(self):
        self.compare_table_struct(source=self.params.get('source'), target=self.params.get('target'),
                                  schemas=self.params.get('schemas'))
        self.__write_excel_report()


if __name__ == '__main__':
    params = {
        # 'url': 'https://bladecloud.hundsun.com/openapi/datacompare/addTableStructureCompare.json', // 测试环境地址
        "source": "Oracle", "target": "DaMeng", "path": "../static/DBDiffResult/"
        # "source": "Mysql", "target": "TdsqlForMysql", "path": "../static/DBDiffResult/", "schemas": ["HSAMLBD"]
    }
    DBC = DBCompare(params)
    DBC.act_compare()